package com.ssy.lingxi.report.dao.settleaccount;

import com.ssy.lingxi.report.model.bo.StatisticsSettlementBO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 平台结算DAO
 * @author 伍将
 * @date 2020/11/26
 * @version 2.0.0
 */
@Service
public class PlatformSettlementDAO {

    @Autowired
    @Qualifier("settleAccountJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询平台结算统计计数
     * @author 伍将
     * @date 2020/11/26
     * @return:
     **/
    public List<StatisticsSettlementBO> getStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" member_id,role_id,")
                .append(" SUM(CASE WHEN status=1 THEN 1 ELSE 0 END) AS ToBeAccountsCount,")
                .append(" SUM(CASE WHEN status=2 THEN 1 ELSE 0 END) AS ToBePayCount,")
                .append(" SUM(CASE WHEN status=3 THEN 1 ELSE 0 END) AS ToBeReceiptCount,")
                .append(" SUM(CASE WHEN status=4 THEN 1 ELSE 0 END) AS CompleteCount")
                .append(" from sa_platform_settlement")
                .append(" group by member_id,role_id")
                .append(" union")
                .append(" select '0' member_id,'0' role_id,")
                .append(" SUM(CASE WHEN status=1 THEN 1 ELSE 0 END) AS ToBeAccountsCount,")
                .append(" SUM(CASE WHEN status=2 THEN 1 ELSE 0 END) AS ToBePayCount,")
                .append(" SUM(CASE WHEN status=3 THEN 1 ELSE 0 END) AS ToBeReceiptCount,")
                .append(" SUM(CASE WHEN status=4 THEN 1 ELSE 0 END) AS CompleteCount")
                .append(" from sa_platform_settlement");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsSettlementBO.class));
    }
}
